/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

/*
 * bzr_vaAnzeigenVA.java
 *
 * Created on 31.03.2011, 09:54:28
 */
package bzr_veranstaltungverwaltung;

import com.mysql.jdbc.Connection;
import com.mysql.jdbc.ResultSet;
import com.mysql.jdbc.Statement;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.DefaultComboBoxModel;
import javax.swing.JComponent;
import javax.swing.JSpinner;
import javax.swing.JTextArea;
import javax.swing.ListSelectionModel;
import javax.swing.table.DefaultTableModel;

/**
 *
 * @author fhiesl
 */
public class bzr_vaAnzeigenVA extends javax.swing.JFrame {

    public Connection con;
    /** Creates new form bzr_vaAnzeigenVA */
    DefaultTableModel dtm_va;
    DefaultTableModel dtm_ma;
    DefaultComboBoxModel dcbm;
    SimpleDateFormat sdf = new SimpleDateFormat("dd.MM.yyyy");

    public bzr_vaAnzeigenVA() throws IllegalAccessException, InstantiationException, SQLException {
        try {
            Class.forName("com.mysql.jdbc.Driver").newInstance();
            String url = "jdbc:mysql://mysql.bezirksrundschau.com:3306/brdbm_1";
            con = (Connection) DriverManager.getConnection(url, "brdbm", "br2011!db");
            dtm_va = new DefaultTableModel();
            tabelleAnlegen_VA();
            tabelleAnlegen_MA();
            initCombo();
            initComponents();
            initSpinners();
            
            if (jTableVA.getRowCount() > 0) {
                jTableVA.setSelectionMode(ListSelectionModel.SINGLE_SELECTION);

            }
        } catch (ClassNotFoundException ex) {
            Logger.getLogger(bzr_vaAnzeigenVA.class.getName()).log(Level.SEVERE, null, ex);
        }



    }

    /** This method is called from within the constructor to
     * initialize the form.
     * WARNING: Do NOT modify this code. The content of this method is
     * always regenerated by the Form Editor.
     */
    @SuppressWarnings("unchecked")
    // <editor-fold defaultstate="collapsed" desc="Generated Code">//GEN-BEGIN:initComponents
    private void initComponents() {

        jScrollPane1 = new javax.swing.JScrollPane();
        jTableVA = new javax.swing.JTable();
        jButtonSchliessen = new javax.swing.JButton();
        jScrollPane2 = new javax.swing.JScrollPane();
        jTable_MA = new javax.swing.JTable();
        jSpinnerVon = new javax.swing.JSpinner();
        jLabel1 = new javax.swing.JLabel();
        jLabel2 = new javax.swing.JLabel();
        jSpinnerNach = new javax.swing.JSpinner();
        jButtonSuchen = new javax.swing.JButton();
        jLabel3 = new javax.swing.JLabel();
        jComboBoxBezirk = new javax.swing.JComboBox();

        setDefaultCloseOperation(javax.swing.WindowConstants.EXIT_ON_CLOSE);

        jTableVA.setModel(dtm_va);
        jTableVA.addFocusListener(new java.awt.event.FocusAdapter() {
            public void focusGained(java.awt.event.FocusEvent evt) {
                jTableVAFocusGained(evt);
            }
        });
        jScrollPane1.setViewportView(jTableVA);

        jButtonSchliessen.setText("Schließen");
        jButtonSchliessen.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonSchliessenActionPerformed(evt);
            }
        });

        jTable_MA.setModel(dtm_ma);
        jScrollPane2.setViewportView(jTable_MA);

        jSpinnerVon.setModel(new javax.swing.SpinnerDateModel());

        jLabel1.setText("Von");

        jLabel2.setText("Bis");

        jSpinnerNach.setModel(new javax.swing.SpinnerDateModel());

        jButtonSuchen.setText("Suchen");
        jButtonSuchen.addActionListener(new java.awt.event.ActionListener() {
            public void actionPerformed(java.awt.event.ActionEvent evt) {
                jButtonSuchenActionPerformed(evt);
            }
        });

        jLabel3.setText("Bezirk");

        jComboBoxBezirk.setModel(dcbm);

        javax.swing.GroupLayout layout = new javax.swing.GroupLayout(getContentPane());
        getContentPane().setLayout(layout);
        layout.setHorizontalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(javax.swing.GroupLayout.Alignment.TRAILING, layout.createSequentialGroup()
                        .addComponent(jButtonSchliessen)
                        .addGap(36, 36, 36))
                    .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING, false)
                        .addComponent(jScrollPane2, javax.swing.GroupLayout.Alignment.LEADING)
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.Alignment.LEADING, javax.swing.GroupLayout.DEFAULT_SIZE, 866, Short.MAX_VALUE)))
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGap(12, 12, 12)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                            .addGroup(layout.createSequentialGroup()
                                .addGap(9, 9, 9)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.TRAILING)
                                    .addComponent(jLabel1)
                                    .addComponent(jLabel2))
                                .addGap(18, 18, 18)
                                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING, false)
                                    .addComponent(jSpinnerNach)
                                    .addComponent(jSpinnerVon, javax.swing.GroupLayout.PREFERRED_SIZE, 70, Short.MAX_VALUE)))
                            .addComponent(jButtonSuchen)))
                    .addGroup(layout.createSequentialGroup()
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addComponent(jLabel3)
                        .addGap(18, 18, 18)
                        .addComponent(jComboBoxBezirk, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)))
                .addContainerGap(38, Short.MAX_VALUE))
        );
        layout.setVerticalGroup(
            layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
            .addGroup(layout.createSequentialGroup()
                .addContainerGap()
                .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.LEADING)
                    .addGroup(layout.createSequentialGroup()
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jSpinnerVon, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jLabel1))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jSpinnerNach, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE)
                            .addComponent(jLabel2))
                        .addPreferredGap(javax.swing.LayoutStyle.ComponentPlacement.UNRELATED)
                        .addGroup(layout.createParallelGroup(javax.swing.GroupLayout.Alignment.BASELINE)
                            .addComponent(jLabel3)
                            .addComponent(jComboBoxBezirk, javax.swing.GroupLayout.PREFERRED_SIZE, javax.swing.GroupLayout.DEFAULT_SIZE, javax.swing.GroupLayout.PREFERRED_SIZE))
                        .addGap(24, 24, 24)
                        .addComponent(jButtonSuchen))
                    .addGroup(layout.createSequentialGroup()
                        .addComponent(jScrollPane1, javax.swing.GroupLayout.PREFERRED_SIZE, 168, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(18, 18, 18)
                        .addComponent(jScrollPane2, javax.swing.GroupLayout.PREFERRED_SIZE, 202, javax.swing.GroupLayout.PREFERRED_SIZE)
                        .addGap(95, 95, 95)
                        .addComponent(jButtonSchliessen)))
                .addContainerGap(118, Short.MAX_VALUE))
        );

        pack();
    }// </editor-fold>//GEN-END:initComponents

    private void jButtonSchliessenActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonSchliessenActionPerformed
        // TODO add your handling code here:
        this.setVisible(false);
    }//GEN-LAST:event_jButtonSchliessenActionPerformed

    private void jTableVAFocusGained(java.awt.event.FocusEvent evt) {//GEN-FIRST:event_jTableVAFocusGained

        if (jTableVA.getSelectedRow() >= 0) {
            try {
                while (dtm_ma.getRowCount() > 0) {
                dtm_ma.removeRow(0);
            }
                Statement s = (Statement) con.createStatement();
                ResultSet rs = (ResultSet) s.executeQuery("SELECT * FROM VA_MA WHERE Veranstaltungen_VA_ID=" + dtm_va.getValueAt(jTableVA.getSelectedRow(), 8));
                while (rs.next()) {
                    int i = (rs.getInt("Freie_Mitarbeiter_MA_ID"));
                    Statement s2 = (Statement) con.createStatement();
                    ResultSet rs2 = (ResultSet) s2.executeQuery("SELECT * FROM Freie_Mitarbeiter WHERE Mitarbeiter_ID=" + i);
                    while (rs2.next()) {
                        String[] row = new String[13];
                        row[0] = rs2.getString("Vorname");
                        row[1] = rs2.getString("Nachname");
                        row[2] = "" + rs2.getInt("Telefonnummer");
                        row[3] = rs2.getString("Email");
                        row[4] = rs2.getString("Bezirk");
                        row[5] = "" + rs2.getDate("GebDatum");
                        row[6] = rs2.getString("Ausbildung");
                        row[7] = "" + rs2.getInt("Konfektionsgroesse");
                        row[8] = rs2.getString("Fuehrerschein");
                        row[9] = "" + rs2.getInt("SVNr");
                        row[10] = rs2.getString("Staatsbuergerschaft");
                        row[11] = "" + rs2.getInt("Bankverbindung");
                        row[12] = rs2.getString("MAArt");
                        dtm_ma.addRow(row);
                    }
                }

            } catch (SQLException ex) {
                Logger.getLogger(bzr_vaAnzeigenVA.class.getName()).log(Level.SEVERE, null, ex);
            }
        }
    }//GEN-LAST:event_jTableVAFocusGained

    private void jButtonSuchenActionPerformed(java.awt.event.ActionEvent evt) {//GEN-FIRST:event_jButtonSuchenActionPerformed
        try {
            // TODO add your handling code here:
            while (dtm_va.getRowCount() > 0) {
                dtm_va.removeRow(0);
            }
            while (dtm_ma.getRowCount() > 0) {
                dtm_ma.removeRow(0);
            }
            Date d = (Date) jSpinnerVon.getValue();
            SimpleDateFormat sd = new SimpleDateFormat("yyyy-MM-dd");
            String dateStringVon = sd.format(d);
            d = (Date) jSpinnerNach.getValue();
            sd = new SimpleDateFormat("yyyy-MM-dd");
            String dateStringNach = sd.format(d);

            Statement s = (Statement) con.createStatement();
            ResultSet rs = (ResultSet) s.executeQuery("SELECT * FROM Veranstaltungen WHERE Startdatum >= '" + dateStringVon + "' AND Startdatum <= '" + dateStringNach + "' AND ZustaendigeGeschaeftsstelle='"+jComboBoxBezirk.getSelectedItem()+"' Order by Startdatum");
            while (rs.next()) {
                String[] row = new String[9];
                row[0] = rs.getString("Name");
                row[1] = rs.getString("Ort");
                row[2] = "" + rs.getInt("PLZ");
                row[3] = rs.getString("Strasse");
                row[4] = "" + rs.getInt("HausNr");
                row[5] = sdf.format(rs.getDate("Startdatum"));
                row[6] = sdf.format(rs.getDate("Enddatum"));
                row[7] = rs.getString("ZustaendigeGeschaeftsstelle");
                row[8] = "" + rs.getInt("Veranstaltungs_id");
                dtm_va.addRow(row);
            }
        } catch (SQLException ex) {
            Logger.getLogger(bzr_vaAnzeigenVA.class.getName()).log(Level.SEVERE, null, ex);
        }

    }//GEN-LAST:event_jButtonSuchenActionPerformed

    /**
     * @param args the command line arguments
     */
    public static void main(String args[]) {
        java.awt.EventQueue.invokeLater(new Runnable() {

            public void run() {
                try {
                    new bzr_vaAnzeigenVA().setVisible(true);
                } catch (IllegalAccessException ex) {
                    Logger.getLogger(bzr_vaAnzeigenVA.class.getName()).log(Level.SEVERE, null, ex);
                } catch (InstantiationException ex) {
                    Logger.getLogger(bzr_vaAnzeigenVA.class.getName()).log(Level.SEVERE, null, ex);
                } catch (SQLException ex) {
                    Logger.getLogger(bzr_vaAnzeigenVA.class.getName()).log(Level.SEVERE, null, ex);
                }
            }
        });
    }
    // Variables declaration - do not modify//GEN-BEGIN:variables
    private javax.swing.JButton jButtonSchliessen;
    private javax.swing.JButton jButtonSuchen;
    private javax.swing.JComboBox jComboBoxBezirk;
    private javax.swing.JLabel jLabel1;
    private javax.swing.JLabel jLabel2;
    private javax.swing.JLabel jLabel3;
    private javax.swing.JScrollPane jScrollPane1;
    private javax.swing.JScrollPane jScrollPane2;
    private javax.swing.JSpinner jSpinnerNach;
    private javax.swing.JSpinner jSpinnerVon;
    private javax.swing.JTable jTableVA;
    private javax.swing.JTable jTable_MA;
    // End of variables declaration//GEN-END:variables

    private void tabelleAnlegen_VA() throws InstantiationException, IllegalAccessException, SQLException {

        dtm_va.addColumn("Name");
        dtm_va.addColumn("Ort");
        dtm_va.addColumn("PLZ");
        dtm_va.addColumn("Straße");
        dtm_va.addColumn("HausNr");
        dtm_va.addColumn("Startdatum");
        dtm_va.addColumn("Enddatum");
        dtm_va.addColumn("Zuständige Geschäftsstelle");
        dtm_va.addColumn("ID");
        Statement st = (Statement) con.createStatement();
        ResultSet rs = (ResultSet) st.executeQuery("SELECT * FROM Veranstaltungen ORDER BY Startdatum");
        
        while (rs.next()) {
            String[] row = new String[9];
            row[0] = rs.getString("Name");
            row[1] = rs.getString("Ort");
            row[2] = "" + rs.getInt("PLZ");
            row[3] = rs.getString("Strasse");
            row[4] = "" + rs.getInt("HausNr");
            row[5] = sdf.format(rs.getDate("Startdatum"));
            row[6] = sdf.format(rs.getDate("Enddatum"));
            row[7] = rs.getString("ZustaendigeGeschaeftsstelle");
            row[8] = "" + rs.getInt("Veranstaltungs_id");
            dtm_va.addRow(row);
        }

    }

    private void tabelleAnlegen_MA() {
        dtm_ma = new DefaultTableModel();
        dtm_ma.addColumn("Vorname");
        dtm_ma.addColumn("Nachname");
        dtm_ma.addColumn("Telefonnummer");
        dtm_ma.addColumn("E-Mail");
        dtm_ma.addColumn("Bezirk");
        dtm_ma.addColumn("GebDatum");
        dtm_ma.addColumn("Ausbildung");
        dtm_ma.addColumn("Konfektionsgröße");
        dtm_ma.addColumn("Führerschein");
        dtm_ma.addColumn("SVNr");
        dtm_ma.addColumn("Staatsbürgerschaft");
        dtm_ma.addColumn("Bankverbindung");
        dtm_ma.addColumn("Art");





    }

    private void initSpinners() {
        JComponent editor_start = new JSpinner.DateEditor(jSpinnerVon, "dd.MM.yyyy");
        jSpinnerVon.setEditor(editor_start);
        JComponent editor_end = new JSpinner.DateEditor(jSpinnerNach, "dd.MM.yyyy");
        jSpinnerNach.setEditor(editor_end);
    }

    private void initCombo() {
        try {
            dcbm = new DefaultComboBoxModel();
            Statement st = (Statement) con.createStatement();
            ResultSet rs = (ResultSet) st.executeQuery("SELECT DISTINCT ZustaendigeGeschaeftsstelle FROM Veranstaltungen");
            while(rs.next()){
                dcbm.addElement(rs.getString("ZustaendigeGeschaeftsstelle"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(bzr_MA_VA_Hinzufügen.class.getName()).log(Level.SEVERE, null, ex);
        }


    }
}
